package com.li.springbootproject.service.test.impl;

import com.li.springbootproject.domain.test.MdOrg;
import com.li.springbootproject.domain.test.OrgTempDO;
import com.li.springbootproject.domain.test.TableClazzDO;
import com.li.springbootproject.mapper.InsertTestMapper;
import com.li.springbootproject.mapper.OrgTempMapper;
import com.li.springbootproject.mapper.test.TableClazzDOMapper;
import com.li.springbootproject.service.test.InsertTestService;
import com.li.springbootproject.utils.SpringContextUtils;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StopWatch;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author LiDong
 * @version 1.0.0
 * @createTime 8/4/2022 8:37 AM
 */
@Service
public class InsertTestServiceImpl implements InsertTestService {

    private static final Logger logger = LoggerFactory.getLogger(InsertTestServiceImpl.class);

    private static final int splitSize = 10000;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private InsertTestMapper insertTestMapper;

    @Autowired
    private TableClazzDOMapper tableClazzDoMapper;

    /**
     * 插入 md_org
     *
     * @return String
     */
    @Override
    public String insertMdOrg() {
        // 插入数据到 md_org 表
        try {
            insertIntoMdOrg();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 插入 orgtemp
     *
     * @return String
     */
    @Override
    public String insertOrgTemp() {
        String requestId = UUID.randomUUID().toString();
        List<OrgTempDO> list = new ArrayList<>();
        List<MdOrg> orgList = null;
        try {
            orgList = getOrgList();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        for (MdOrg org : orgList) {
            OrgTempDO orgTempDO = new OrgTempDO();
            orgTempDO.setId(UUID.randomUUID().toString());
            orgTempDO.setRequestId(requestId);
            orgTempDO.setCode(org.getCode());
            orgTempDO.setType("ORG");
            list.add(orgTempDO);
        }
        // 写固化表测试
        // insertOneByOneIntoOrgTempTest(list);
        multiValueInsert(list);
        // multiValueInsertBySql(list);
        // multiInsertWithSqlSession(list);
        // multiInsertWithJdbc(list);

        // 创建临时表插入
        // createTempTable();
        // multiValueInsertBySql(list);
        // multiValueInsertNotAutoSubmit(list);
        // List<OrgTempDO> orgTempDOList = insertTestMapper.list();
        return "写表完成";
    }

    /**
     * 插入 table_class
     *
     * @return String
     */
    @Override
    public String insertTableClazz() {
        List<TableClazzDO> list = new ArrayList<>();
        List<MdOrg> orgList = null;
        try {
            orgList = getOrgList();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        Random random1 = new Random();
        for (int i = 0; i < 500000; i++) {
            TableClazzDO tableClazz = new TableClazzDO();
            tableClazz.setId(UUID.randomUUID().toString());
            tableClazz.setBillcode("CLASS_" + i);
            tableClazz.setCreatetime(new Date());
            tableClazz.setBilldate(new Date());
            tableClazz.setClassNo(String.valueOf(i));
            tableClazz.setUnitcode(orgList.get(random1.nextInt(orgList.size())).getCode());
            list.add(tableClazz);
        }
        List<List<TableClazzDO>> lists = splitList(list, 10000);
        for (List<TableClazzDO> tableClazzDOS : lists) {
            tableClazzDoMapper.insertBatch(tableClazzDOS);
        }
        return null;
    }

    /**
     * 多 value 值插入 sqlSession
     *
     * @param list list
     */
    private void multiValueInsertNotAutoSubmit(List<OrgTempDO> list) {
        SqlSessionFactory sqlSessionFactory = SpringContextUtils.getBean(SqlSessionFactory.class);
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        InsertTestMapper mapper = sqlSession.getMapper(InsertTestMapper.class);

        List<List<OrgTempDO>> lists = splitList(list, 10000);
        InsertTestMapper insertTestMapper = SpringContextUtils.getBean(InsertTestMapper.class);
        StopWatch stopWatch = new StopWatch("totalTask");
        stopWatch.start();
        for (List<OrgTempDO> orgTempList : lists) {
            StopWatch stopWatch1 = new StopWatch("task1");
            stopWatch1.start();
            mapper.insertOrgTempBatch(orgTempList);
            stopWatch1.stop();
            System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());
        }
        sqlSession.commit();
        sqlSession.close();
        stopWatch.stop();
        System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());
    }

    private void createTempTable() {
        insertTestMapper.dropTable();
        insertTestMapper.createOrgTempTable();
    }

    /**
     * 多 value 值插入
     *
     * @param list list
     */
    private void multiValueInsertBySql(List<OrgTempDO> list) {
        List<List<OrgTempDO>> lists = splitList(list, 10000);
        InsertTestMapper insertTestMapper = SpringContextUtils.getBean(InsertTestMapper.class);
        StopWatch stopWatch = new StopWatch("totalTask");
        stopWatch.start();
        for (List<OrgTempDO> orgTempList : lists) {
            StopWatch stopWatch1 = new StopWatch("task1");
            stopWatch1.start();
            insertTestMapper.insertOrgTempBatch(orgTempList);
            stopWatch1.stop();
            System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());
        }
        stopWatch.stop();
        System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());
    }

    /**
     * ps.executeBatch()
     *
     * @param list list
     */
    private void multiInsertWithJdbc(List<OrgTempDO> list) {
        try {
            Connection connection = SpringContextUtils.getBean(JdbcTemplate.class).getDataSource().getConnection();
            String sql = "insert into orgtemp(id, request_id,code,type) VALUES (?,?,?,?)";
            PreparedStatement ps = null;
            try {
                ps = connection.prepareStatement(sql);
                // 取消自动提交
                connection.setAutoCommit(false);
                StopWatch stopWatch = new StopWatch("totalTask");
                stopWatch.start();
                for (int i = 1; i <= list.size(); i++) {
                    OrgTempDO orgTempDO = list.get(i - 1);
                    ps.setObject(1, orgTempDO.getId());
                    ps.setObject(2, orgTempDO.getRequestId());
                    ps.setObject(3, orgTempDO.getCode());
                    ps.setObject(4, orgTempDO.getType());
                    ps.addBatch();
                    if (i % 10000 == 0) {
                        StopWatch stopWatch1 = new StopWatch("task1");
                        stopWatch1.start();
                        ps.executeBatch();
                        ps.clearBatch();
                        stopWatch1.stop();
                        System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());
                    }
                }
                ps.executeBatch();
                ps.clearBatch();
                connection.commit();//所有语句都执行完毕后才手动提交sql语句
                stopWatch.stop();
                System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                connection.close();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * sqlSession 手动提交事务
     *
     * @param list list
     */
    private void multiInsertWithSqlSession(List<OrgTempDO> list) {
        SqlSessionFactory sqlSessionFactory = SpringContextUtils.getBean(SqlSessionFactory.class);
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        InsertTestMapper mapper = sqlSession.getMapper(InsertTestMapper.class);

        List<List<OrgTempDO>> lists = splitList(list, splitSize);
        StopWatch stopWatch = new StopWatch("totalTask");
        stopWatch.start();
        for (List<OrgTempDO> orgTempList : lists) {
            // StopWatch stopWatch1 = new StopWatch("task1");
            // stopWatch1.start();
            mapper.insertOrgTempBatch(orgTempList);
            // stopWatch1.stop();
            // System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());
        }
        sqlSession.commit();
        sqlSession.close();
        stopWatch.stop();
        System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());

    }

    /**
     * 多 value 值插入
     *
     * @param list list
     */
    private void multiValueInsert(List<OrgTempDO> list) {
        List<List<OrgTempDO>> lists = splitList(list, splitSize);
        OrgTempMapper orgTempMapper = SpringContextUtils.getBean(OrgTempMapper.class);
        StopWatch stopWatch = new StopWatch("totalTask");
        stopWatch.start();
        for (List<OrgTempDO> orgTempList : lists) {
            // StopWatch stopWatch1 = new StopWatch("task1");
            // stopWatch1.start();
            orgTempMapper.insertBatchOrgTemp(orgTempList);
            // stopWatch1.stop();
            // System.out.println("写入 10000 条执行耗时：{}" + stopWatch1.getTotalTimeMillis());
        }
        stopWatch.stop();
        System.out.println("写入 150000 条执行耗时：{}" + stopWatch.getTotalTimeMillis());
    }

    /**
     * Md_org 批量插入
     *
     * @throws ParseException parse exception
     */
    private void insertIntoMdOrg() throws ParseException {
        List<MdOrg> orgList = getOrgList();
        // insertWithJdbcTemplate(orgList);
        // insertOneByOneUseInsertSelective(orgList);
        // insertOneByOneJdbcExcute(orgList);
        // insertWithJdbcUpdatePrepare(orgList);
        multiValueInsertMdOrg(orgList);
    }

    /**
     * 多 value 值插入
     *
     * @param orgList orgList
     */
    private void multiValueInsertMdOrg(List<MdOrg> orgList) {
        List<List<MdOrg>> lists = splitList(orgList, 10000);
        StopWatch stopWatch1 = new StopWatch("insert md_org task");
        StopWatch stopWatch2 = new StopWatch("all task");
        stopWatch1.start();
        stopWatch2.start();
        for (List<MdOrg> list : lists) {
            insertTestMapper.insertMdOrgBatch(list);
            stopWatch1.stop();
            logger.info(String.valueOf(stopWatch1.getTotalTimeMillis()));
            stopWatch1.start();
        }
        stopWatch2.stop();
        logger.info("总任务执行用时：" + stopWatch2.getTotalTimeMillis());
    }

    /**
     * jdbcTemplate.batchUpdate
     *
     * @param orgList orgList
     */
    private void insertWithJdbcUpdatePrepare(List<MdOrg> orgList) {
        String sql = "INSERT INTO md_org (ID,VER,CODE,ORGCODE,NAME,SHORTNAME,VALIDTIME,INVALIDTIME,PARENTCODE,ORDINAL,CREATEUSER,CREATETIME,PARENTS) VALUES\n" +
                "\t (?,?,?,?,?,?,?,?,?,?,?,?,?);\n";
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setObject(1, orgList.get(i).getId());
                ps.setObject(2, orgList.get(i).getVer());
                ps.setObject(3, orgList.get(i).getCode());
                ps.setObject(4, orgList.get(i).getOrgcode());
                ps.setObject(5, orgList.get(i).getName());
                ps.setObject(6, orgList.get(i).getShortname());
                ps.setObject(7, orgList.get(i).getValidtime());
                ps.setObject(8, orgList.get(i).getInvalidtime());
                ps.setObject(9, orgList.get(i).getParentcode());
                ps.setObject(10, orgList.get(i).getOrdinal());
                ps.setObject(11, orgList.get(i).getCreateuser());
                ps.setObject(12, orgList.get(i).getCreatetime());
                ps.setObject(13, orgList.get(i).getParents());
            }

            @Override
            public int getBatchSize() {
                return orgList.size();
            }
        });

    }

    /**
     * jdbcTemplate
     *
     * @param orgList orgList
     */
    private void insertOneByOneJdbcExcute(List<MdOrg> orgList) {
        String sql = "INSERT INTO md_org (ID,VER,CODE,ORGCODE,NAME,SHORTNAME,VALIDTIME,INVALIDTIME,PARENTCODE,ORDINAL,CREATEUSER,CREATETIME,PARENTS) VALUES\n" +
                "\t (?,?,?,?,?,?,?,?,?,?,?,?,?);\n";
        List<Object[]> objectList = new ArrayList<>();
        for (MdOrg mdOrg : orgList) {
            Object[] object = {
                    mdOrg.getId(), mdOrg.getVer(), mdOrg.getCode(), mdOrg.getOrgcode(), mdOrg.getName(), mdOrg.getShortname(), mdOrg.getValidtime(), mdOrg.getInvalidtime(),
                    mdOrg.getParentcode(), mdOrg.getOrdinal(), mdOrg.getCreateuser(), mdOrg.getCreatetime(), mdOrg.getParents()
            };
            objectList.add(object);
        }
        StopWatch stopWatch = new StopWatch("task1");
        stopWatch.start();
        int i = 0;
        for (Object[] objects : objectList) {
            jdbcTemplate.update(sql, objects);
            i++;
            if (i == 10000) {
                stopWatch.stop();
                logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));
                i = 0;
            }
        }
    }


    /**
     * 逐条插入
     *
     * @param orgList orgList
     */
    private void insertOneByOneUseInsertSelective(List<MdOrg> orgList) {
        StopWatch stopWatch = new StopWatch("task1");
        stopWatch.start();
        int i = 0;
        for (MdOrg mdOrg : orgList) {
            // testMapper.insertSelective(mdOrg);
            i++;
            if (i == 10000) {
                stopWatch.stop();
                logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));
                i = 0;
            }
        }
        // 10000 条 需要 4分29秒
    }

    /**
     * JdbcTemplate sql 插入
     *
     * @param orgList orgList
     */
    private void insertWithJdbcTemplate(List<MdOrg> orgList) {
        String sql = "INSERT INTO md_org (ID,VER,CODE,ORGCODE,NAME,SHORTNAME,VALIDTIME,INVALIDTIME,PARENTCODE,ORDINAL,CREATEUSER,CREATETIME,PARENTS) VALUES\n" +
                "\t (?,?,?,?,?,?,?,?,?,?,?,?,?);\n";
        List<Object[]> objectList = new ArrayList<>();
        for (MdOrg mdOrg : orgList) {
            Object[] object = {
                    mdOrg.getId(), mdOrg.getVer(), mdOrg.getCode(), mdOrg.getOrgcode(), mdOrg.getName(), mdOrg.getShortname(), mdOrg.getValidtime(), mdOrg.getInvalidtime(),
                    mdOrg.getParentcode(), mdOrg.getOrdinal(), mdOrg.getCreateuser(), mdOrg.getCreatetime(), mdOrg.getParents()
            };
            objectList.add(object);
        }
        List<List<Object[]>> lists = splitList(objectList, 10000);

        // List<Object[]> tempList = new ArrayList<>();
        // tempList.add(objectList.get(0));
        // jdbcTemplate.batchUpdate(sql, tempList);

        StopWatch stopWatch = new StopWatch("task1");
        stopWatch.start();
        for (List<Object[]> list : lists) {
            jdbcTemplate.batchUpdate(sql, list);
            logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));
        }
        stopWatch.stop();
        logger.info(String.valueOf(stopWatch.getTotalTimeMillis()));
    }

    /**
     * 组装 orgList 数据
     *
     * @return List
     * @throws ParseException
     */
    private List<MdOrg> getOrgList() throws ParseException {
        String validTimeStr = "1970-01-01 00:00:00";
        String invalidTimeStr = "9999-12-31 00:00:00";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date validTime = simpleDateFormat.parse(validTimeStr);
        Date inValidTime = simpleDateFormat.parse(invalidTimeStr);
        List<MdOrg> orgList = new ArrayList<MdOrg>();
        for (int i = 0; i < 10000; i++) {
            MdOrg org = new MdOrg();
            org.setId(UUID.randomUUID().toString());
            org.setVer("2" + i);
            org.setCode("D" + i);
            org.setOrgcode("D" + i);
            org.setName("机构D" + i);
            org.setShortname("机构D" + i);
            org.setValidtime(validTime);
            org.setInvalidtime(inValidTime);
            // org.setParentcode("A1");
            org.setParentcode("B11");
            org.setOrdinal("2" + i);
            org.setCreateuser("sys_user_admin");
            org.setCreatetime(new Date());
            // org.setParents("");
            org.setParents("B11/D" + i);
            orgList.add(org);
        }
        return orgList;
    }

    public static <T> List<List<T>> splitList(List<T> list, int limit) {
        int length = list.size();
        // 计算可以分成多少组
        int num = (length + limit - 1) / limit;
        List<List<T>> newList = new ArrayList<>(num);
        for (int i = 0; i < num; i++) {
            // 开始位置
            int fromIndex = i * limit;
            // 结束位置
            int toIndex = Math.min((i + 1) * limit, length);
            newList.add(list.subList(fromIndex, toIndex));
        }
        return newList;
    }

}
